/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.mlu.jdbc.simple;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.mlu.jdbc.simple.support.CustomBeanMapper;
import org.mlu.jdbc.simple.support.PropertyBeanMapper;
import org.mlu.jdbc.simple.utils.JdbcUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * Exposes jdbc operations thereby delegating opening and closing of database resources.
 * 
 * @author Mlungisi Sincuba
 * @since 1.0
 */
public class SimpleJdbc {

    private DataSource dataSource;
    private static Logger logger = LoggerFactory.getLogger(SimpleJdbc.class);

    /**
     * 
     * @param dataSource DataSource for database. It must be on properties file with keys/values like <br/>
     * <ul>
     * <li>driver=org.postgresql.Driver</li>
     * <li>url=jdbc:postgresql://localhost:4444/gain</li>
     * <li>user=gain</li>
     * <li>pwd=gain</li>
     * <li>minPool=1</li>
     * <li>maxPool=15</li>
     * <li>maxSize=30</li>
     * </ul>
     */
    public SimpleJdbc(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     *
     * @param sql query
     * @param type to cast to
     * @return object for a particular type
     *
     * @see org.apache.commons.dbutils.QueryRunner#query(java.lang.String,
     * org.apache.commons.dbutils.ResultSetHandler)
     */
    public <T extends Object> T queryForObject(String sql, Class<T> type) {
        ResultSet rs = getResultSet(sql);
        T t = null;
        try {
            t = getType(rs, t, type);
        } catch (SQLException ex) {
            logger.error("queryForObject with params could not be run", ex);
        }
        return t;
    }

    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param type to cast to 
     * @param params arguments to sql
     * @return object for a particular type
     */
    public <T> T queryForObject(String sql, Class<T> type, Object... params) {
        ResultSet rs = getResultSet(sql, params);
        T t = null;
        try {
            t = getType(rs, t, type);
        } catch (SQLException ex) {
            logger.error("queryForObject with params could not be run", ex);
        }
        return t;
    }

    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param resultSetColumnBeanProperty
     * @param type to cast to
     * @return object for a particular type
     */
    public <T> T queryForObject(String sql, Map<String, String> resultSetColumnBeanProperty, Class<T> type) {
        return new CustomBeanMapper().mapRow(dataSource, sql, type, resultSetColumnBeanProperty);
    }

    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param type to cast to
     * @param params arguments to sql
     * @return object for a particular type
     */
    public <T> T queryForBean(String sql, Class<T> type, Object... params) {
        return new PropertyBeanMapper().mapRow(dataSource, sql, type, params);
    }

    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param type to cast to
     * @return object for a particular type
     */
    public <T> List<T> queryForList(String sql, Class<T> type) {
        List<T> tList = null;
        QueryRunner run = new QueryRunner(dataSource);
        ResultSetHandler<List<T>> handler = new BeanListHandler<T>(type);
        try {
            tList = run.query(sql, handler);
        } catch (SQLException ex) {
            logger.error("queryForObject could not be run", ex);
        }
        return tList;
    }

    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param type to cast to
     * @param params arguments to sql
     * @return object for a particular type
     */
    public <T> List<T> queryForList(String sql, Class<T> type, Object... params) {
        List<T> tList = null;
        QueryRunner run = new QueryRunner(dataSource);
        ResultSetHandler<List<T>> handler = new BeanListHandler<T>(type);
        try {
            tList = run.query(sql, handler, params);
        } catch (SQLException ex) {
            logger.error("queryForList with params could not be run", ex);
        }
        return tList;
    }

    /**
     * 
     * @param sql ddl query to database
     * @return number of rows affected
     */
    public int update(String sql) {
        QueryRunner run = new QueryRunner(dataSource);
        try {
            return run.update(sql);
        } catch (SQLException ex) {
            logger.error("update with sql: " + sql + " failed with error code: " + ex.getErrorCode() + " and state: " + ex.getSQLState(), ex);
        }
        return 0;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param con database connection
     * @return number of rows affected
     */
    public int update(String sql, Connection con) {
        QueryRunner run = new QueryRunner(dataSource);
        try {
            return run.update(con, sql);
        } catch (SQLException ex) {
            logger.error("update with sql: " + sql + " failed.", ex);
        }
        return 0;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param params arguments to sql
     * @return number of rows affected
     */
    public int update(String sql, Object... params) {
        QueryRunner run = new QueryRunner(dataSource);
        try {
            return run.update(sql, params);
        } catch (SQLException ex) {
            logger.error("update with sql: " + sql + " failed.", ex);
        }
        return 0;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param con database connection
     * @param params arguments to sql
     * @return number of rows affected
     */
    public int update(String sql, Connection con, Object... params) {
        QueryRunner run = new QueryRunner(dataSource);
        try {
            return run.update(con, sql, params);
        } catch (SQLException ex) {
            logger.error("update with sql: " + sql + " failed.", ex);
        }
        return 0;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param params arguments to sql
     * @return number of rows affected per batch
     */
    public int[] batch(String sql, Object[][] params) {
        QueryRunner run = new QueryRunner(dataSource);
        try {
            return run.batch(sql, params);
        } catch (SQLException ex) {
            logger.error("batch with sql: " + sql + " failed.", ex);
        }
        return null;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param params
     * @param con database connection
     * @return number of rows affected per batch
     */
    public int[] batch(String sql, Object[][] params, Connection con) {
        QueryRunner run = new QueryRunner(dataSource);
        try {
            return run.batch(con, sql, params);
        } catch (SQLException ex) {
            logger.error("batch with sql: " + sql + " failed.", ex);
        }
        return null;
    }

    private ResultSet getResultSet(String sql) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection(dataSource);
            statement = JdbcUtils.getPreparedStatement(connection, sql);
            rs = statement.executeQuery();
        } catch (SQLException ex) {
            logger.error("getResultSet could not be run", ex);
        }
        return rs;
    }
    
    private ResultSet getResultSet(String sql, Object ...params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection(dataSource);
            statement = JdbcUtils.getPreparedStatement(connection, sql, params);
            rs = statement.executeQuery();
        } catch (SQLException ex) {
            logger.error("getResultSet could not be run", ex);
        }
        return rs;
    }

    private <T extends Object> T getType(ResultSet rs, T t, Class<T> type) throws SQLException {
        if (rs.next()) {
            t = (T) JdbcUtils.getResultSetValue(rs, 1, type);
        }
        return t;
    }
    
}
